﻿using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.SS.Util;

namespace Common.Download
{
    /// <summary>
    /// 下载工具
    /// </summary>
    public class DownloadUtils
    {



        /// <summary>
        /// 拼接需要保存的文件全路径
        /// </summary>
        /// <param name="saveFolderPath">保存文件的文件夹路径</param>
        /// <param name="fileName">文件名称</param>
        /// <param name="fileExtension">文件扩展名</param>
        /// <returns></returns>
        public static string CombineFileAllPath(FolderPath saveFolderPath, string fileName, FileExtension fileExtension)
        {
            var contentRootPath = Environment.CurrentDirectory; //获取当前应用（网站）的根目录
            var savePath = $"{contentRootPath}\\wwwroot\\DownLoad\\{saveFolderPath}";
            var pathExist = Directory.Exists(savePath);
            if (pathExist == false)
            {
                Directory.CreateDirectory(savePath);
            }
            return Path.Combine(savePath, $"{fileName}.{fileExtension}");
        }

        /// <summary>
        /// 生成文件的下载url路径
        /// </summary>
        /// <param name="request">HttpRequest</param>
        /// <param name="downLoadFolderPath">需要下载文件的文件夹的路径</param>
        /// <param name="fileName">文件名称</param>
        /// <param name="fileExtension">需要下载文件的扩展名</param>
        /// <returns></returns>
        public static string GenerateDownloadUrl(Microsoft.AspNetCore.Http.HttpRequest request, FolderPath downLoadFolderPath, string fileName, FileExtension fileExtension)
        {
            var contentRootPath = Environment.CurrentDirectory; //获取当前应用（网站）的根目录
            var fileAllPath = $"{contentRootPath}\\wwwroot\\DownLoad\\{downLoadFolderPath}\\{fileName}.{fileExtension}";
            var fileExist = File.Exists(fileAllPath);

            if (fileExist == false)
            {
                throw new Exception("The downloaded file does not exist");
            }

            return $"{request.Scheme}://{request.Host}/DownLoad/{downLoadFolderPath}/{fileName}.{fileExtension}";
        }

        /// <summary>
        /// 生成文件下载url路径
        /// </summary>
        /// <param name="request">HttpRequest</param>
        /// <param name="localFileAllPath">本地文件全路径</param>
        /// <returns></returns>
        public static string GenerateDownloadUrl(Microsoft.AspNetCore.Http.HttpRequest request, string localFileAllPath)
        {
            var fileExist = File.Exists(localFileAllPath);

            if (fileExist == false)
            {
                throw new Exception("The downloaded file does not exist");
            }

            var index = localFileAllPath.LastIndexOf(@"\DownLoad\", StringComparison.Ordinal);
            var curString = localFileAllPath.Substring(index).Replace(@"\", "/");
            var downloadUrl = $"{request.Scheme}://{request.Host}{curString}";
            return downloadUrl;
        }

        /// <summary>
        /// 创建excel文件
        /// </summary>
        /// <param name="title">标题</param>
        /// <param name="explain">说明</param>
        /// <param name="fileAllName">文件全名称</param>
        /// <param name="cellTitleSource">列标题来源</param>
        /// <param name="dataSource">需要下载的数据源</param>
        /// <param name="totalColumn">添加统计的行(List集合中添加的是统计列的名称)</param>
        /// <param name="callback">回调函数</param>
        public static void CreateNewExcelFile(string title, string explain, string fileAllName, List<DownloadField> cellTitleSource, DataTable dataSource, List<string> totalColumn, Action<HSSFWorkbook> callback)
        {
            var workbook = new HSSFWorkbook();
            var sheet = workbook.CreateSheet();
            sheet.DefaultColumnWidth = 80 * 256;//300 * 256; 

            //获取总列数
            var totalCellNumber = cellTitleSource.Count;

            var addRow = 3;
            if (totalColumn != null)
            {
                addRow += 1;
            }

            //创建Excel的行和行中的单元格
            for (int i = 0; i < dataSource.Rows.Count + addRow; i++)
            {
                //创建行
                var newRow = sheet.CreateRow(i);

                //设置单元格的高
                newRow.Height = 10 * 20;//35 * 20;
                newRow.HeightInPoints = 15;//35;


                //创建列
                for (int j = 0; j < totalCellNumber; j++)
                {
                    //sheet.SetColumnWidth(j, 30 * 256); //设置单元格的宽度
                    sheet.SetColumnWidth(j, 9 * 256); //设置单元格的宽度
                    newRow.CreateCell(j);
                }
            }

            //设置Execl标题
            //合并单元格
            sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, totalCellNumber - 1)); //起始行  结束行 起始列 结束列

            for (int j = 0; j < totalCellNumber; j++)
            {
                sheet.GetRow(0).GetCell(j).CellStyle = SetCellStyle(workbook, StyleType.Title);
            }
            sheet.GetRow(0).GetCell(0).SetCellValue(title);

            //设置Excel说明
            sheet.AddMergedRegion(new CellRangeAddress(1, 1, 0, totalCellNumber - 1)); //起始行  结束行 起始列 结束列

            for (var j = 0; j < totalCellNumber; j++)
            {
                sheet.GetRow(1).GetCell(j).CellStyle = SetCellStyle(workbook, StyleType.Explain);
            }

            sheet.GetRow(1).GetCell(0).SetCellValue(explain);

            //设置列标题
            //获取dataGridView中的数据
            //DataGridViewColumnCollection coll = cellTitleSource.Columns;

            var bindingRelationship = new Dictionary<string, string>();

            foreach (var item in cellTitleSource)
            {
                //绑定的列名,显示的列名
                bindingRelationship.Add(item.Field, item.Title);
            }


            IRow currentRow = null;
            var cellIndex = 0;
            var rowIndex = 2;

            foreach (var item in bindingRelationship)
            {
                currentRow = sheet.GetRow(rowIndex);
                //currentRow.Height = 200 * 20;
                //currentRow.HeightInPoints = 200;
                var currentCell = currentRow.GetCell(cellIndex);
                currentCell.SetCellValue(item.Value);
                currentCell.CellStyle = SetCellStyle(workbook, StyleType.CellTitle);
                ++cellIndex;
            }

            var tempTotal = new Dictionary<int, decimal>();

            rowIndex = 3;

            //遍历需要下载的数据源
            foreach (DataRow item in dataSource.Rows)
            {
                currentRow = sheet.GetRow(rowIndex);
                var k = 0;
                foreach (var item2 in bindingRelationship)
                {
                    if (string.IsNullOrEmpty(item2.Key)) continue;
                    var dataTypeName = dataSource.Columns[item2.Key].DataType.Name;

                    var currentCell = currentRow.GetCell(k);

                    var curCellValue = item[item2.Key];
                    if (curCellValue == null)
                    {
                        ++k;
                        continue;
                    }

                    if (curCellValue is DBNull)
                    {
                        ++k;
                        continue;
                    }
                    if (dataTypeName == nameof(String))
                    {
                        currentCell.SetCellValue(curCellValue.ToString());
                    }
                    else if (dataTypeName == nameof(Decimal) || dataTypeName == nameof(Double) || dataTypeName == nameof(Single) || dataTypeName == nameof(Int16) || dataTypeName == nameof(Int32) || dataTypeName == nameof(Int64))
                    {
                        currentCell.SetCellValue(Convert.ToDouble(curCellValue));
                    }
                    else if (dataTypeName == nameof(DateTime))
                    {
                        currentCell.SetCellValue($"{curCellValue}");
                        //currentCell.SetCellValue(Convert.ToDateTime(curCellValue));
                        //currentCell.SetCellValue(Convert.ToDateTime(curCellValue));
                    }
                    else if (dataTypeName == nameof(Boolean))
                    {
                        currentCell.SetCellValue(Convert.ToBoolean(curCellValue));
                    }
                    else
                    {
                        currentCell.SetCellValue("");
                    }

                    ++k;

                    if (totalColumn == null) continue;

                    if (!totalColumn.Contains(item2.Value)) continue;

                    var value = item[item2.Key]?.ToString();
                    if (string.IsNullOrWhiteSpace(value))
                    {
                        value = "0";
                    }
                    if (tempTotal.ContainsKey(currentCell.ColumnIndex))
                    {
                        tempTotal[currentCell.ColumnIndex] =
                            tempTotal[currentCell.ColumnIndex] + Convert.ToDecimal(value);
                    }
                    else
                    {
                        tempTotal.Add(currentCell.ColumnIndex, Convert.ToDecimal(value));  //key:列序号,value:    

                    }

                }

                ++rowIndex;
            }

            if (tempTotal.Count != 0)
            {
                //拿到最后一行
                currentRow = sheet.GetRow(rowIndex);
                sheet.GetRow(rowIndex).GetCell(0).CellStyle = SetCellStyle(workbook, StyleType.Title);
                sheet.GetRow(rowIndex).GetCell(0).SetCellValue("统计");

                for (var i = 0; i < currentRow.LastCellNum; i++)
                {
                    currentRow.GetCell(i).CellStyle = SetCellStyle(workbook, StyleType.Content);
                }


                foreach (var item in tempTotal)
                {
                    var currentCell = currentRow.GetCell(item.Key);
                    currentCell.SetCellValue(item.Value.ToString());
                    currentCell.CellStyle = SetCellStyle(workbook, StyleType.Content);
                }
            }

            //桌面路径
            //string strDesktopPath = Environment.GetFolderPath(Environment.SpecialFolder.DesktopDirectory);
            //拼接路径
            //strDesktopPath = strDesktopPath + @"\" + title + DateTime.Now.ToString("yyyyMMddHHmmssffff") + ".xls";

            if (File.Exists(fileAllName))
            {
                File.Delete(fileAllName);
            }


            //保存数据
            using (var fsWrite = File.OpenWrite(fileAllName))
            {
                workbook.Write(fsWrite);
            }

            callback?.Invoke(workbook);  //执行回调

        }

        /// <summary>
        /// 创建excel文件
        /// </summary>
        /// <param name="title">标题</param>
        /// <param name="explain">说明</param>
        /// <param name="fileAllName">文件全名称</param>
        /// <param name="cellTitleSource">列标题来源</param>
        /// <param name="dataSource">需要下载的数据源</param>
        /// <param name="totalColumn">添加统计的行(List集合中添加的是统计列的名称)</param>
        /// <param name="callback">回调函数</param>
        public static void CreateNewExcelFile<T>(string title, string explain, string fileAllName, List<DownloadField> cellTitleSource, List<T> dataSource, List<string> totalColumn, Action<HSSFWorkbook> callback)
        {
            var workbook = new HSSFWorkbook();
            var sheet = workbook.CreateSheet();
            sheet.DefaultColumnWidth = 80 * 256;//300 * 256; 

            //获取总列数
            var totalCellNumber = cellTitleSource.Count;

            var addRow = 3;
            if (totalColumn != null)
            {
                addRow += 1;
            }

            //创建Excel的行和行中的单元格
            for (int i = 0; i < dataSource.Count + addRow; i++)
            {
                //创建行
                var newRow = sheet.CreateRow(i);

                //设置单元格的高
                newRow.Height = 10 * 20;//35 * 20;
                newRow.HeightInPoints = 15;//35;


                //创建列
                for (int j = 0; j < totalCellNumber; j++)
                {
                    //sheet.SetColumnWidth(j, 30 * 256); //设置单元格的宽度
                    sheet.SetColumnWidth(j, 9 * 256); //设置单元格的宽度
                    newRow.CreateCell(j);
                }
            }

            //设置Execl标题
            //合并单元格
            sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, totalCellNumber - 1)); //起始行  结束行 起始列 结束列

            for (int j = 0; j < totalCellNumber; j++)
            {
                sheet.GetRow(0).GetCell(j).CellStyle = SetCellStyle(workbook, StyleType.Title);
            }
            sheet.GetRow(0).GetCell(0).SetCellValue(title);

            //设置Excel说明
            sheet.AddMergedRegion(new CellRangeAddress(1, 1, 0, totalCellNumber - 1)); //起始行  结束行 起始列 结束列

            for (var j = 0; j < totalCellNumber; j++)
            {
                sheet.GetRow(1).GetCell(j).CellStyle = SetCellStyle(workbook, StyleType.Explain);
            }

            sheet.GetRow(1).GetCell(0).SetCellValue(explain);

            //设置列标题
            //获取dataGridView中的数据
            //DataGridViewColumnCollection coll = cellTitleSource.Columns;

            var bindingRelationship = new Dictionary<string, string>();

            foreach (var item in cellTitleSource)
            {
                //绑定的列名,显示的列名
                bindingRelationship.Add(item.Field, item.Title);
            }


            IRow currentRow = null;
            var cellIndex = 0;
            var rowIndex = 2;

            foreach (var item in bindingRelationship)
            {
                currentRow = sheet.GetRow(rowIndex);
                //currentRow.Height = 200 * 20;
                //currentRow.HeightInPoints = 200;
                var currentCell = currentRow.GetCell(cellIndex);
                currentCell.SetCellValue(item.Value);
                currentCell.CellStyle = SetCellStyle(workbook, StyleType.CellTitle);
                ++cellIndex;
            }

            var tempTotal = new Dictionary<int, decimal>();

            rowIndex = 3;

            //遍历需要下载的数据源
            foreach (var item in dataSource)
            {
                currentRow = sheet.GetRow(rowIndex);
                var properties = item.GetType().GetProperties().ToList();

                var k = 0;
                foreach (var item2 in bindingRelationship)
                {
                    if (string.IsNullOrEmpty(item2.Key))
                    {
                        ++k;
                        continue;
                    }

                    var currentCell = currentRow.GetCell(k);

                    var curProperty = properties.Find(x => x.Name == item2.Key);

                    #region 转换实际类型

                    if (curProperty.GetValue(item) == null)
                    {
                        ++k;
                        continue;
                    }

                    var curPropertyType = curProperty.PropertyType.Name;
                    if (curPropertyType == typeof(string).Name)
                    {
                        currentCell.SetCellValue(curProperty.GetValue(item)?.ToString());
                    }
                    else if (curPropertyType == typeof(int).Name)
                    {
                        currentCell.SetCellValue(Convert.ToInt32(curProperty.GetValue(item)));
                    }
                    else if (curPropertyType == typeof(decimal).Name || curPropertyType == typeof(double).Name || curPropertyType == typeof(float).Name)
                    {
                        currentCell.SetCellValue(Convert.ToDouble(curProperty.GetValue(item)));
                    }
                    else if (curPropertyType == typeof(DateTime).Name)
                    {
                        currentCell.SetCellValue(Convert.ToDateTime(curProperty.GetValue(item)));
                    }
                    else if (curPropertyType == typeof(bool).Name)
                    {
                        currentCell.SetCellValue(Convert.ToBoolean(curProperty.GetValue(item)));
                    }
                    else
                    {
                        currentCell.SetCellValue("");
                    }
                    #endregion

                    ++k;


                    #region 计算合计逻辑
                    if (totalColumn == null) continue;
                    if (!totalColumn.Contains(item2.Value)) continue;
                    var value = curProperty.GetValue(item)?.ToString();
                    if (string.IsNullOrWhiteSpace(value))
                    {
                        value = "0";
                    }
                    if (tempTotal.ContainsKey(currentCell.ColumnIndex))
                    {
                        tempTotal[currentCell.ColumnIndex] =
                            tempTotal[currentCell.ColumnIndex] + Convert.ToDecimal(value);
                    }
                    else
                    {
                        tempTotal.Add(currentCell.ColumnIndex, Convert.ToDecimal(value));  //key:列序号,value:   
                    }
                    #endregion


                }

                ++rowIndex;
            }

            if (tempTotal.Count != 0)
            {
                //拿到最后一行
                currentRow = sheet.GetRow(rowIndex);
                sheet.GetRow(rowIndex).GetCell(0).CellStyle = SetCellStyle(workbook, StyleType.Title);
                sheet.GetRow(rowIndex).GetCell(0).SetCellValue("统计");

                for (var i = 0; i < currentRow.LastCellNum; i++)
                {
                    currentRow.GetCell(i).CellStyle = SetCellStyle(workbook, StyleType.Content);
                }


                foreach (var item in tempTotal)
                {
                    var currentCell = currentRow.GetCell(item.Key);
                    currentCell.SetCellValue(item.Value.ToString());
                    currentCell.CellStyle = SetCellStyle(workbook, StyleType.Content);
                }
            }

            //桌面路径
            //string strDesktopPath = Environment.GetFolderPath(Environment.SpecialFolder.DesktopDirectory);
            //拼接路径
            //strDesktopPath = strDesktopPath + @"\" + title + DateTime.Now.ToString("yyyyMMddHHmmssffff") + ".xls";

            if (File.Exists(fileAllName))
            {
                File.Delete(fileAllName);
            }


            //保存数据
            using (var fsWrite = File.OpenWrite(fileAllName))
            {
                workbook.Write(fsWrite);
            }

            callback?.Invoke(workbook);  //执行回调

        }

        #region 内部成员
        /// <summary>
        /// 设置单元格样式
        /// </summary>
        /// <param name="workbook">当前当前工作簿对象</param>
        /// <param name="styleType">样式类型</param>
        /// <returns></returns>
        private static ICellStyle SetCellStyle(HSSFWorkbook workbook, StyleType styleType)
        {
            //创建单元格设置对象
            var cellStyle = workbook.CreateCellStyle();

            //创建设置字体对象
            var font = workbook.CreateFont();

            switch (styleType)
            {
                case StyleType.Title:
                    //设置水平、垂直居中
                    cellStyle.Alignment = HorizontalAlignment.Center;
                    cellStyle.VerticalAlignment = VerticalAlignment.Center;
                    //单元格填充颜色
                    //cellStyle.FillForegroundColor = HSSFColor.ROYAL_BLUE.index;//HSSFColor.PALE_BLUE.index;
                    //cellStyle.FillPattern = HSSFCellStyle.SOLID_FOREGROUND;
                    //设置边框
                    cellStyle.BorderBottom = BorderStyle.None;
                    cellStyle.BorderLeft = BorderStyle.None;
                    cellStyle.BorderRight = BorderStyle.None;
                    cellStyle.BorderTop = BorderStyle.None;

                    //创建设置字体对象
                    font.FontHeightInPoints = 16;//16;//设置字体大小
                    font.Boldweight = short.MinValue; //加粗
                    font.FontName = "Arial";
                    cellStyle.SetFont(font);
                    break;
                case StyleType.Explain:
                    //设置水平、垂直居中
                    cellStyle.Alignment = HorizontalAlignment.Left;
                    cellStyle.VerticalAlignment = VerticalAlignment.Center;
                    //单元格填充颜色
                    //cellStyle.FillForegroundColor = HSSFColor.TURQUOISE.index;//HSSFColor.PALE_BLUE.index;
                    //cellStyle.FillPattern = HSSFCellStyle.SOLID_FOREGROUND;
                    //设置边框
                    cellStyle.BorderBottom = BorderStyle.None;
                    cellStyle.BorderLeft = BorderStyle.None;
                    cellStyle.BorderRight = BorderStyle.None;
                    cellStyle.BorderTop = BorderStyle.None;

                    //创建设置字体对象
                    font.FontHeightInPoints = 14;//14;//设置字体大小
                    font.Boldweight = short.MinValue; //加粗
                    font.FontName = "Arial";
                    cellStyle.SetFont(font);
                    break;
                case StyleType.CellTitle:
                    //设置水平、垂直居中
                    cellStyle.Alignment = HorizontalAlignment.Center;
                    cellStyle.VerticalAlignment = VerticalAlignment.Center;
                    //单元格填充颜色
                    //cellStyle.FillForegroundColor = HSSFColor.AQUA.index;//HSSFColor.PALE_BLUE.index;
                    //cellStyle.FillPattern = HSSFCellStyle.SOLID_FOREGROUND;
                    //设置边框
                    cellStyle.BorderBottom = BorderStyle.None;
                    cellStyle.BorderLeft = BorderStyle.None;
                    cellStyle.BorderRight = BorderStyle.None;
                    cellStyle.BorderTop = BorderStyle.None;

                    //创建设置字体对象
                    font.FontHeightInPoints = 12;//12;//设置字体大小
                    font.Boldweight = short.MaxValue; //加粗
                    font.FontName = "Arial";
                    cellStyle.SetFont(font);
                    break;
                case StyleType.Content:
                    //设置水平、垂直居中
                    cellStyle.Alignment = HorizontalAlignment.Center;
                    cellStyle.VerticalAlignment = VerticalAlignment.Center;
                    //设置边框
                    cellStyle.BorderBottom = BorderStyle.None;
                    cellStyle.BorderLeft = BorderStyle.None;
                    cellStyle.BorderRight = BorderStyle.None;
                    cellStyle.BorderTop = BorderStyle.None;

                    //创建设置字体对象
                    font.FontHeightInPoints = 6;//10;//设置字体大小
                    font.FontName = "Arial";
                    cellStyle.SetFont(font);
                    break;
                default:
                    break;
            }
            return cellStyle;

        }

        private enum StyleType
        {
            /// <summary>
            /// 文件标题
            /// </summary>
            Title = 0,
            /// <summary>
            /// 说明
            /// </summary>
            Explain = 1,

            /// <summary>
            /// 列标题
            /// </summary>
            CellTitle = 2,

            /// <summary>
            /// 内容
            /// </summary>
            Content = 3
        }

        /// <summary>
        /// 下载时需要绑定的字段
        /// </summary>
        public class DownloadField
        {
            /// <summary>
            /// 绑定的字段
            /// </summary>
            public string Field { get; set; }

            /// <summary>
            /// 标题
            /// </summary>
            public string Title { get; set; }
        }
        #endregion


        public enum FolderPath
        {
            /// <summary>
            /// excel文件夹
            /// </summary>
            Excel = 1,
        }

        public enum FileExtension
        {
            /// <summary>
            /// excel文件扩展名
            /// </summary>
            Xlsx = 1,
        }
    }
}